**Housekeeping
clear all
cap log close
log using ${rep_root}/logs/condense_arrests.log, text replace
set more off

use ${rep_root}/data/arrests_all

**Drop arrest records that are identical save for having different line numbers (which were inserted after the fact)
drop line_no
duplicates drop

**Clean up central booking number
destring cb_no, force gen(cb_num)
drop if missing(cb_num) | cb_num==0
drop cb_num

**Remove any CB numbers that appear more than once
sort cb_no
by cb_no: gen cb_ct = _N
drop if cb_ct>1

**Merge on cleaned arrest data to get MID and officer role
sort cb_no
merge 1:m cb_no using ${rep_root}/data/FOIA/FOIA_unified/arrests, keep(3) keepusing(mid officer_role) nogen
drop if missing(mid)

**Save a dataset that tells us which MIDs ever had any arrests in their careers
preserve
sort mid
by mid: keep if _n==1
keep mid
save ${rep_root}/data/mids_with_arrests, replace
restore

**Extract arrest date information
gen arrest_date = substr(arrest_date_time, 1, 11)
gen arrest_dt = date(arrest_date, "DMY")
gen arrest_yr = year(arrest_dt)
gen arrest_m = month(arrest_dt)

**Extract information on who was the lead officer in each arest
gen first_off = officer_role == "FIRST ARRESTING OFFICER"

**Mark some arrests as "public order"
gen public_order = inlist(fbi_code, "22", "24", "26", "MCC", "TRF")

**Create indicators for the combinations of first officer and public order
gen fo_po = first_off==1 & public_order==1
gen nfo_po = first_off==0 & public_order==1
gen fo_npo = first_off==1 & public_order==0
gen nfo_npo = first_off==0 & public_order==0

gen t1_anyo = 0
replace t1_anyo = 1 if inlist(fbi_code, "01A", "02", "03", "04A", "04B")
replace t1_anyo = 1 if inlist(fbi_code, "05", "06", "07", "09")

**Total 
sort mid arrest_yr arrest_m
foreach var in fo_po nfo_po fo_npo nfo_npo t1_anyo{
	by mid arrest_yr arrest_m: egen `var'_tot = total(`var')
}

**Make unique by officer/yearmonth
by mid arrest_yr arrest_m: keep if _n==1

keep mid arrest_yr arrest_m *_tot

**Create balance panel by officer/yearmonth
fillin mid arrest_yr arrest_m

**If an officer has no observations in a yearmonth, that means 0 arrests of any kind
foreach var of varlist *_tot{
	replace `var' = 0 if _fillin==1
}
drop _fillin

**Create totals of different kinds of arrests
gen arrests = fo_po_tot + nfo_po_tot + fo_npo_tot + nfo_npo_tot
gen first_off = fo_po_tot + fo_npo_tot 
gen serious = fo_npo_tot + nfo_npo_tot
gen serious_first_off = fo_npo_tot
rename t1_anyo_tot type1_arrests

drop *_tot

**Generate Stata yearmonth variable
gen yearmo = ym(arrest_yr, arrest_m)

tab yearmo

sum arrests, d
sum arrests if arrests>0, d

save ${rep_root}/data/arrests_panel, replace
clear
log close


